﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using IDAL;
using Model;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;

namespace DAL
{
    public class UserLgoinDAL : IUserLgoinDAL
    {

        // private int UL_userId;

        // private string UI_userName;

        // private string UI_password;

        //private System.DateTime UL_lastLoginTime;

        // private int UL_status;
        /// <summary>
        /// 根据传入的userId判断一条数据是否存在
        /// </summary>       
        public bool userIdExists(int userId)
        {
            //Set up a return value
            bool isExist = false;
            string sqlStatement = "SELECT UL_userId FROM UserLgoin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }


        /// <summary>
        /// 根据传入的userName判断一条数据是否存在
        /// </summary>       
        public bool userNameExists(string userName)
        {
            //Set up a return value
            bool isExist = false;
            string sqlStatement = "SELECT UL_userName FROM UserLgoin WHERE UI_userName = @userName";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@userName", userName);

                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        isExist = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isExist;

        }

        /// <summary>
        /// 根据传入的adminName得到密码
        /// </summary>
        public string getPsdByName(string userName)
        {
            //Set up a return value
            string password = null;
            string sqlStatement = "SELECT AD_adminPsd FROM Admin WHERE AD_adminName = @adminName";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@adminName", adminName);

                //Execute the delete	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                        password = Convert.ToString(rdr.GetString(0));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return password;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>                
        public bool Add(int userId, string userName, string password, System.DateTime lastLoginTime, int status)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "INSERT INTO UserLgoin VALUES(@userId, @userName, @password,@lastLoginTime,@status )";


            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userId), new SqlParameter("@userName", userName), new SqlParameter("@password", password), new SqlParameter("@lastLoginTime", lastLoginTime), new SqlParameter("@status", status) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.UserLogin userLogin)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "INSERT INTO UserLgoin VALUES(@userId, @userName, @password,@lastLoginTime,@status )";


            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userLogin.getUL_userId()), new SqlParameter("@userName", userLogin.getUI_userName()), new SqlParameter("@password", userLogin.getUI_password()), new SqlParameter("@lastLoginTime", userLogin.getUL_lastLoginTime()), new SqlParameter("@status", userLogin.getUL_status()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 更新一条数据
        /// </summary>          
        public bool Update(int userId, string userName, string password, System.DateTime lastLoginTime, int status)
        {     //Set up a return value
            bool opetation = false;
            string sqlStatement = "UPDATE UserLgoin SET UL_userId=@userId, UL_userName=@userName,UL_password=@password,UL_lastLoginTime=@lastLoginTime,UL_status=@status";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userId), new SqlParameter("@userName", userName), new SqlParameter("@password", password), new SqlParameter("@lastLoginTime", lastLoginTime), new SqlParameter("@status", status) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }


        /// <summary>
        /// 修改用户信息
        /// </summary>
        public bool Update(Model.UserLogin userLogin)
        {     //Set up a return value
            bool opetation = false;
            string sqlStatement = "UPDATE UserLgoin SET UL_userId=@userId, UL_userName=@userName,UL_password=@password,UL_lastLoginTime=@lastLoginTime,UL_status=@status";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter[] paras;
                paras = new SqlParameter[] { new SqlParameter("@userId", userLogin.getUL_userId()), new SqlParameter("@userName", userLogin.getUI_userName()), new SqlParameter("@password", userLogin.getUI_password()), new SqlParameter("@lastLoginTime", userLogin.getUL_lastLoginTime()), new SqlParameter("@status", userLogin.getUL_status()) };

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, paras);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        /// <summary>
        /// 删除一个login通过ID
        /// </summary>
        public bool userIdDelete(int userId)
        {
            //Set up a return value
            bool opetation = false;
            string sqlStatement = "DELETE FROM UserLgoin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                int lineNum = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlStatement, para);
                {
                    if (lineNum > 0)
                        opetation = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return opetation;
        }



        /// <summary>
        /// 根据ID查询用户对象
        /// </summary>
        public Model.UserLogin userIsExist(int userId)
        {

            //Set up a return value
            bool opetation = false;
            Model.UserLogin userLogin = null;
            string sqlStatement = "SELECT * FROM UserLgoin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        userLogin.setUL_userId(Convert.ToInt32(rdr.GetString(0)));
                        userLogin.setUI_userName(Convert.ToString(rdr.GetString(1)));
                        userLogin.setUI_password(Convert.ToString(rdr.GetString(2)));
                        userLogin.setUL_lastLoginTime(Convert.ToDateTime(rdr.GetString(3)));
                        userLogin.setUL_status(Convert.ToInt32(rdr.GetString(4)));
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return userLogin;
        }


        /// <summary>
        /// 根据ID查看账号是否被限制
        /// </summary>
        public int isLimit(int userId)
        {
            //Set up a return value
            int status = 0;

            string sqlStatement = "SELECT UL_status FROM UserLgoin WHERE UL_userId = @userId";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@userId", userId);

                //Execute the delete	
                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        status = Convert.ToInt32(rdr.GetString(0));

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return status;
        }



        /// <summary>
        /// 根据名字查看账号是否被限制
        /// </summary>
        public int isLimit(string userName)
        {
            //Set up a return value
            int status = 0;

            string sqlStatement = "SELECT UL_status FROM UserLgoin WHERE UL_userName = @userName";

            try
            {
                SqlConnection conn = new SqlConnection("Data Source=localhost;Initial Catalog=Tiamat;Integrated Security=True");

                SqlParameter para;
                para = new SqlParameter("@userName", userName);

                //Execute the delete	
                //Execute the query	
                using (SqlDataReader rdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sqlStatement, para))
                {
                    if (rdr.Read())
                    {
                        status = Convert.ToInt32(rdr.GetString(0));

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return status;
        }

    }
}
